package mysql

import (
	"database/sql"
	"errors"
	"fmt"
	"log"
	"strings"

	"gitee.com/zibianqu/fyzdb/connection"
	"gitee.com/zibianqu/fyzdb/lib"
	"gitee.com/zibianqu/fyzdb/lib/mts"
	"gitee.com/zibianqu/fyzdb/lib/str"
	"gitee.com/zibianqu/fyzdb/lib/variable"
	"github.com/spf13/cast"
)

var mapping = "fcl"

type whereObj struct {
	field   string
	operate string
	value   string
	bind    bool
}

type Builder struct {
	SessionId  int64
	connection string
	// 字段映射
	mapping string
	// 事务
	field      []string
	table      []string
	join       []string
	where      []whereObj
	order      []string
	skip       int
	take       int
	limit      string
	group      []string
	having     string
	bindParams map[string]any
	sql        string
	logSql     string
	log        bool
}

func Table(table ...string) *Builder {
	var builder = &Builder{
		connection: "default",
		table:      table,
		logSql:     "",
		log:        false,
		mapping:    mapping,
	}
	return builder
}

func Connection(conn string) *Builder {
	var builder = &Builder{
		connection: conn,
		logSql:     "",
		log:        false,
		mapping:    mapping,
	}
	return builder
}

func (b *Builder) GetSessionId() int64 {
	return b.SessionId
}

func (b *Builder) SetSessionId(sid int64) connection.IBuilder {
	b.SessionId = sid
	return b
}

func (b *Builder) Table(table ...string) connection.IBuilder {
	var builder = &Builder{
		connection: b.connection,
		table:      table,
		logSql:     "",
		log:        false,
		mapping:    mapping,
	}
	return builder
}

func (b *Builder) getDb() *sql.DB {
	db := Mysql().GetDb(b.connection)
	return db
}

// @param table 表名 sys_user as a
// @param onWhere 条件
func (b *Builder) Join(table, onWhere string) connection.IBuilder {
	sqlStr := fmt.Sprintf("inner join %s on %s", table, onWhere)
	b.join = append(b.join, sqlStr)
	return b
}

// @param table 表名 sys_user as a
// @param onWhere 条件
func (b *Builder) LeftJoin(table, onWhere string) connection.IBuilder {
	sqlStr := fmt.Sprintf("left join %s on %s", table, onWhere)
	b.join = append(b.join, sqlStr)
	return b
}

// @param table 表名 sys_user as a
// @param onWhere 条件
func (b *Builder) RightJoin(table, onWhere string) connection.IBuilder {
	sqlStr := fmt.Sprintf("right join %s on %s", table, onWhere)
	b.join = append(b.join, sqlStr)
	return b
}

// @param field 第1个参数字段名称，可以'a.UserId'、'UserId'
// @param operate 第2个参数，可能为链接符，如=，> < 等;可能为条件值operate为`=`
// @param value 第3个参数，可能为是否绑定，也可能为条件的值
// @param isBind 4个参数，第四个参数为是否需要绑定参数
func (b *Builder) Where(params ...any) connection.IBuilder {
	length := len(params)
	if length < 1 {
		log.Fatal("builder.go: params is error !")
	}
	field, operate, value, bind := "", "=", "", false
	bParams := make([]any, 0)
	for k, val := range params {
		if v, ok := val.(string); ok && k == 0 {
			field = v
			continue
		}
		if v, ok := val.(bool); ok {
			bind = v
			continue
		}
		bParams = append(bParams, val)
	}

	if len(bParams) == 0 {
		operate = ""
	}

	if len(bParams) == 1 {
		value = cast.ToString(bParams[0])
	}

	if len(bParams) == 2 {
		operate = cast.ToString(bParams[0])
		value = cast.ToString(bParams[1])
	}

	b.where = append(b.where, whereObj{field: field, operate: operate, value: value, bind: bind})
	return b
}

// 排序
func (b *Builder) OrderBy(params ...string) connection.IBuilder {
	length := len(params)
	if length < 1 {
		log.Fatal("params is error !")
	}
	field, direct := "", ""
	field = params[0]
	if length == 2 {
		direct = params[1]
	}
	b.order = append(b.order, field+" "+direct)
	return b
}

func (b *Builder) Skip(offset int) connection.IBuilder {
	b.skip = offset
	return b
}

func (b *Builder) Take(limit int) connection.IBuilder {
	b.take = limit
	return b
}

func (b *Builder) Group(field string) connection.IBuilder {
	b.group = append(b.group, field)
	return b
}

func (b *Builder) Having(raw string) connection.IBuilder {
	b.having = raw
	return b
}

func (b *Builder) Select(fields ...string) connection.IBuilder {
	length := len(fields)
	if length < 1 {
		log.Fatal("params is error !")
	}
	for _, v := range fields {
		b.field = append(b.field, v)
	}
	return b
}

// 日志开启
func (b *Builder) Log() connection.IBuilder {
	b.log = true
	return b
}

// 获取日志sql
func (b *Builder) GetLogSql() string {
	return b.logSql
}

func (b *Builder) Get(dest ...any) []map[string]any {
	sql, bindParam := b.assembleQuerySql()
	if b.log {
		return nil
	}
	result := b.Query(sql, bindParam)
	if result == nil {
		return result
	}
	length := len(dest)
	if length > 0 {
		// 赋值的结构体
		for _, v := range dest {
			// lib.Map0Struct(result, v)
			mts.Covert(result, v, b.mapping)
		}
	}
	return result
}

// @param dest 赋值目标结构体
func (b *Builder) First(dest ...any) map[string]any {
	b.Skip(0)
	b.Take(1)
	sql, bindParam := b.assembleQuerySql()
	if b.log {
		return nil
	}
	res := b.Query(sql, bindParam)
	if res == nil {
		return nil
	}
	result := res[0]
	length := len(dest)
	if length > 0 {
		// 赋值的结构体
		for _, v := range dest {
			// lib.Map0Struct(result, v)
			mts.Covert(result, v, b.mapping)
		}
	}
	return result
}

func (b *Builder) FetchAll(sql string, bindParam ...any) []map[string]any {
	result := b.Query(sql, bindParam)
	return result
}

func (b *Builder) FetchOne(sql string, bindParam ...any) map[string]any {
	aSql := strings.ToLower(sql)
	if !str.Obj().Contains(aSql, " limit ") {
		sql += " limit 1"
	}
	res := b.Query(sql, bindParam)
	if res == nil {
		return nil
	}
	result := res[0]
	return result
}

func (b *Builder) Count(fieldSql ...string) int64 {
	length := len(fieldSql)
	b.field = make([]string, 0)
	if length > 0 {
		fSql := fieldSql[0]
		b.field = append(b.field, "count("+fSql+") as Number")
	} else {
		b.field = append(b.field, "count(1) as Number")
	}
	sql, bindParam := b.assembleQuerySql()
	if b.log {
		return 0
	}
	res := b.Query(sql, bindParam)
	if res == nil {
		return 0
	}
	result := res[0]
	return result["Number"].(int64)
}

func (b *Builder) Sum(Column string) any {
	b.field = make([]string, 0)
	b.field = append(b.field, "sum("+Column+") as Number")
	sql, bindParam := b.assembleQuerySql()
	if b.log {
		return 0
	}
	res := b.Query(sql, bindParam)
	if res == nil {
		return 0
	}
	result := res[0]
	return result["Number"]
}

func (b *Builder) Value(column string) any {
	b.Skip(0)
	b.Take(1)
	b.field = make([]string, 0)
	b.field = append(b.field, column)
	sql, bindParam := b.assembleQuerySql()
	if b.log {
		return 0
	}
	res := b.Query(sql, bindParam)
	if res == nil {
		return 0
	}
	result := res[0]
	return result[column]
}

func (b *Builder) Pluck(column string) []any {
	b.field = make([]string, 0)
	b.field = append(b.field, column)
	sql, bindParam := b.assembleQuerySql()
	if b.log {
		return nil
	}
	res := b.Query(sql, bindParam)
	if res == nil {
		return nil
	}
	result := make([]any, 0)
	for _, val := range res {
		if v, ok := val[column]; ok {
			result = append(result, v)
		}
	}
	return result
}

// ======

// 查询语句组装
func (b *Builder) assembleQuerySql() (string, []any) {
	field, tables := "*", ""
	// 表字段
	if b.field != nil {
		field = strings.Join(b.field, ",")
	}
	// 表名
	if b.table == nil {
		lib.ThrowError(errors.New(" table is error !"))
	}
	tables = strings.Join(b.table, ",")
	sql := "select " + field + " from " + tables
	// 左右连接
	if len(b.join) > 0 {
		sql = fmt.Sprintf("%s %s", sql, strings.Join(b.join, " "))
	}
	// bindParams := make([]any, 0)
	bpToMap := make(map[string]any)
	// 条件
	if b.where != nil {
		whereArr := make([]string, 0)
		for _, v := range b.where {
			wstr := v.field + " " + v.operate
			if !b.log && v.bind {
				/* wstr += " ?"
				bindParams = append(bindParams, v.value) */
				bindField := getRealField(v.field)
				wstr += " :" + bindField
				bpToMap[bindField] = v.value
			} else {
				if v.value != "" {
					wstr += " '" + v.value + "' "
				}

			}
			whereArr = append(whereArr, wstr)
		}
		sql += " where " + strings.Join(whereArr, " and ")
	}

	if b.group != nil {
		sql += " group by " + strings.Join(b.group, ",")
		if b.having != "" {
			sql += "  having " + b.having
		}
	}

	// 排序
	if b.order != nil {
		sql += " order by " + strings.Join(b.order, ",")
	}

	if b.take > 0 {
		//pageIndex := lib.Str.Conversion(lib.If(b.skip >= 1, (b.skip-1)*b.take, 0))
		pageIndex := cast.ToString(b.skip)
		sql += " limit " + pageIndex + " , " + cast.ToString(b.take)
	}
	b.BindParam(bpToMap)
	bpArr := make([]any, 0)
	sql, bpArr, b.logSql = sqlBindParam(sql, b.bindParams, b.log)
	return sql, bpArr
}

// 参数绑定
func (b *Builder) BindParam(bpToMap map[string]any) connection.IBuilder {
	if b.bindParams == nil {
		b.bindParams = make(map[string]any)
	}
	for k, v := range bpToMap {
		b.bindParams[k] = v
	}
	return b
}

func (b *Builder) Stmt(sqlStr string) (*sql.Stmt, error) {
	if b.SessionId > 0 {
		tx := Session(b.SessionId).Tx
		if tx != nil {
			return tx.Prepare(sqlStr)
		}
	}
	return Mysql().GetDb(b.connection).Prepare(sqlStr)
}

// @link http://www.360doc.com/content/22/0427/15/78746357_1028559956.shtml 事务
// p1: sql, p2: bindparam
func (b *Builder) Query(strs ...any) []map[string]any {
	length := len(strs)
	if length < 1 {
		log.Fatal("params is error !")
	}
	sqlStr := cast.ToString(strs[0])
	bindParams := make([]any, 0)
	if length > 1 {
		bindParams = strs[1].([]any)
	}
	fields := make([]string, 0)
	fieldTypes := make([]*sql.ColumnType, 0)

	stmt, err := b.Stmt(sqlStr)
	lib.ThrowError(err)
	// 可能有问题，如果是事务的话
	defer stmt.Close()
	result, err := stmt.Query(bindParams...)
	lib.ThrowError(err)
	fields, err = result.Columns()
	lib.ThrowError(err)
	fieldTypes, err = result.ColumnTypes()
	lib.ThrowError(err)
	var rows []map[string]any
	defer result.Close()
	for result.Next() {
		acceptRow := make([]any, len(fields))
		row := make(map[string]any)
		for i := range acceptRow {
			acceptRow[i] = &acceptRow[i]
		}
		err = result.Scan(acceptRow...)
		lib.ThrowError(err)
		for i, v := range acceptRow {
			tp := fieldTypes[i].DatabaseTypeName()
			val, err := FieldReflect(fields[i], tp, v)
			lib.ThrowError(err)
			row[fields[i]] = val
		}
		rows = append(rows, row)
	}
	return rows
}

func (b *Builder) Insert(save any) (rowsAffect int64, lastInsertId int64) {
	if b.table == nil {
		lib.ThrowError(errors.New(" table is error !"))
	}
	tables := strings.Join(b.table, ",")
	sql := "insert into " + tables + "("
	var tmp = make(map[string]any)

	mts.Covert(save, &tmp, b.mapping)
	fieldParam := make([]string, 0)
	insertParam := make([]string, 0)
	insertValue := make([]string, 0)
	bindParam := make([]any, 0)
	for k, v := range tmp {
		fieldParam = append(fieldParam, k)
		insertParam = append(insertParam, "?")
		insertValue = append(insertValue, "\""+cast.ToString(v)+"\"")
		bindParam = append(bindParam, v)
	}

	// 日志
	b.logSql = sql
	b.logSql += strings.Join(fieldParam, ",") + ") value(" + strings.Join(insertValue, ",") + ")"
	sql += strings.Join(fieldParam, ",") + ") value(" + strings.Join(insertParam, ",") + ")"
	if b.log {
		return 0, 0
	}
	stmt, err := b.Stmt(sql)
	lib.ThrowError(err)
	defer stmt.Close()
	result, err := stmt.Exec(bindParam...)
	lib.ThrowError(err)
	lastInsertId, err = result.LastInsertId()
	lib.ThrowError(err)
	rowsAffect, err = result.RowsAffected()
	lib.ThrowError(err)
	return
}

// 插入多条数据
func (b *Builder) InsertMany(list []any) (rowsAffect int64, lastInsertId int64) {
	if b.table == nil {
		lib.ThrowError(errors.New(" table is error !"))
	}
	tables := strings.Join(b.table, ",")
	sql := "insert into " + tables + "("
	tmpList := make([]map[string]any, 0)
	for _, val := range list {
		// tmpList = append(tmpList, lib.Map0Struct(val, b.mapping).(map[string]any))
		tmpList = append(tmpList, mts.CovertMap[string, any](val, b.mapping))
	}

	bindParam := make([]any, 0)
	valueArr := make([]string, 0)
	logValueArr := make([]string, 0)
	fieldParam := make([]string, 0)
	for _, val := range tmpList {
		mapInfo := val
		insertParam := make([]string, 0)
		// 用于日志
		insertValue := make([]string, 0)
		if variable.IsEmpty(fieldParam) {
			for k, _ := range mapInfo {
				fieldParam = append(fieldParam, k)
			}
		}
		for _, f := range fieldParam {
			insertValue = append(insertValue, "\""+cast.ToString(mapInfo[f])+"\"")
			insertParam = append(insertParam, "?")
			bindParam = append(bindParam, mapInfo[f])
		}
		valueArr = append(valueArr, "("+strings.Join(insertParam, ",")+")")
		logValueArr = append(logValueArr, "("+strings.Join(insertValue, ",")+")")
	}
	// 日志
	b.logSql = sql
	b.logSql += strings.Join(fieldParam, ",") + ") values" + strings.Join(logValueArr, ",")
	sql += strings.Join(fieldParam, ",") + ") values" + strings.Join(valueArr, ",")
	if b.log {
		return 0, 0
	}

	stmt, err := b.Stmt(sql)
	lib.ThrowError(err)
	defer stmt.Close()
	result, err := stmt.Exec(bindParam...)
	lib.ThrowError(err)
	lastInsertId, err = result.LastInsertId()
	lib.ThrowError(err)
	rowsAffect, err = result.RowsAffected()
	lib.ThrowError(err)
	return
}

func (b *Builder) Update(save any) (rowsAffect int64) {
	tmp := make(map[string]any)
	// lib.Map0Struct(save, &tmp, b.mapping)
	mts.Covert(save, &tmp, b.mapping)
	updateParam := make([]string, 0)
	bpToMap := make(map[string]any)
	for k, v := range tmp {
		bindField := getRealField(k)
		bpToMap[bindField] = v
		if b.log {
			updateParam = append(updateParam, k+"='"+cast.ToString(v)+"'")
		} else {
			updateParam = append(updateParam, k+"= :"+bindField)
		}
	}
	// 表名
	if b.table == nil {
		lib.ThrowError(errors.New(" table is error !"))
	}
	tables := strings.Join(b.table, ",")
	sql := "update " + tables + " set " + strings.Join(updateParam, ",")
	// 条件
	if b.where != nil {
		whereArr := make([]string, 0)
		for _, v := range b.where {
			wstr := v.field + " " + v.operate
			if !b.log && v.bind {
				bindField := getRealField(v.field)
				wstr += " :" + bindField
				bpToMap[bindField] = v.value
			} else {
				if v.value != "" {
					wstr += " '" + v.value + "' "
				}
			}
			whereArr = append(whereArr, wstr)
		}
		sql += " where " + strings.Join(whereArr, " and ")
	}

	bindParams := make([]any, 0)
	sql, bindParams, b.logSql = sqlBindParam(sql, bpToMap, b.log)
	if b.log {
		return 0
	}

	stmt, err := b.Stmt(sql)
	lib.ThrowError(err)
	defer stmt.Close()
	result, err := stmt.Exec(bindParams...)
	lib.ThrowError(err)
	rowsAffect, err = result.RowsAffected()
	lib.ThrowError(err)
	return
}

// 删除
func (b *Builder) Delete() (rowsAffect int64) {
	// 表名
	if b.table == nil {
		lib.ThrowError(errors.New(" table is error !"))
	}
	tables := strings.Join(b.table, ",")
	sql := "delete from " + tables
	bpToMap := make(map[string]any)
	// 条件
	if b.where != nil {
		whereArr := make([]string, 0)
		for _, v := range b.where {
			wstr := v.field + " " + v.operate
			if !b.log && v.bind {
				bindField := getRealField(v.field)
				wstr += " :" + bindField
				bpToMap[bindField] = v.value
			} else {
				if v.value != "" {
					wstr += " '" + v.value + "' "
				}
			}
			whereArr = append(whereArr, wstr)
		}
		sql += " where " + strings.Join(whereArr, " and ")
	}

	bindParams := make([]any, 0)
	sql, bindParams, b.logSql = sqlBindParam(sql, bpToMap, b.log)
	if b.log {
		return 0
	}

	stmt, err := b.Stmt(sql)
	lib.ThrowError(err)
	defer stmt.Close()
	result, err := stmt.Exec(bindParams...)
	lib.ThrowError(err)
	rowsAffect, err = result.RowsAffected()
	lib.ThrowError(err)
	return
}

func (b *Builder) Execute(sql string, bps ...any) (rowsAffect int64) {
	bpLen := len(bps)
	bpToMap := make(map[string]any)
	bindParams := make([]any, 0)
	if bpLen == 1 {
		if v, ok := bps[0].(map[string]any); ok {
			bpToMap = v
		} else {
			bindParams = bps
		}
	}
	if bpLen > 1 {
		bindParams = bps
	}
	if len(bpToMap) != 0 {
		sql, bindParams, b.logSql = sqlBindParam(sql, bpToMap, b.log)
	}

	stmt, err := b.Stmt(sql)
	lib.ThrowError(err)
	defer stmt.Close()
	result, err := stmt.Exec(bindParams...)
	lib.ThrowError(err)
	rowsAffect, err = result.RowsAffected()
	lib.ThrowError(err)
	return
}
